淺談 SQL Server 日期時間查詢與精度進位陷阱
TLDR
- 建議全面採用
datetime2或datetimeoffset，避免使用datetime與smalldatetime。 - 針對
datetime2的精度設定，建議根據業務需求明確指定(0)、(3)或(7)，不要依賴框架預設值。 - 時間處理應優先在應用程式層完成，並統一來源，避免資料庫層與應用層的時間誤差。
- 進行時間區間查詢時，請一律使用
Start <= Time < End的寫法，避免使用AddTicks(-1)搭配<=造成的精度進位與誤判問題。 - 避免在查詢條件的欄位上進行運算（如
DATEADD），以確保索引（Index）能被有效利用。 - 使用
Dapper或ADO.NET時，需注意參數型別對應，避免因型別不一致導致的隱式轉換或查詢失敗。
SQL Server 的日期時間型別
在 SQL Server 中，不同日期時間型別的精度與適用場景差異顯著。
| 型別 | 精度 (Precision) | 說明 |
|---|---|---|
| datetime | 3.33 毫秒 | 舊系統產物，毫秒非連續，會自動進位。 |
| smalldatetime | 1 分鐘 | 精度極低，秒數會四捨五入進位。 |
| datetime2 | 100 奈秒 | 新專案首選，精度高且範圍廣。 |
| datetimeoffset | 100 奈秒 | 包含時區偏移量，適合跨國應用。 |
TIP
實務上，應全面棄用 smalldatetime 與 datetime。smalldatetime 範圍僅至 2079 年，存在潛在的「千年蟲」風險；而 datetime 則已被 datetime2 取代。此外，若從 Oracle 遷移資料，datetime2 支援 0001 年起的日期，能避免 datetime 僅支援 1753 年後的寫入限制。
關於精度的選擇 (0)、(3)、(7)
- 精度
(0)(秒)：適用於不需要高精度顯示的場景，可避免前端顯示與後端儲存不一致導致的查詢失敗。 - 精度
(3)(毫秒)：業界通用標準，兼顧效能與解析度。 - 精度
(7)(100 奈秒)：預設值，適用於科學運算，但需注意硬體時鐘是否支援此解析度。
WARNING
若使用 Entity Framework Code First，請務必顯式指定精度（如 .HasPrecision(0)），切勿依賴框架預設值，以免未來升級或遷移時發生非預期的行為。
時間來源的選擇：應用層 vs 資料庫層
在非分散式架構中，建議將時間處理統一在應用程式層，而非依賴資料庫的 GETDATE()。
- 應用層處理：可避免應用伺服器與資料庫伺服器時間不同步的問題。
- SARGability 考量：對欄位進行運算（如
DATEADD(MINUTE, -1, RecordTime) = GETDATE()）會導致索引搜尋（Index Seek）退化為索引掃描（Index Scan），應盡量避免。
時間區間的閉合處理
在處理時間區間查詢時，應避免使用 AddTicks(-1) 這種容易引發精度誤判的寫法。
錯誤示範
csharp
// ❌ 不推薦：容易因精度差異導致誤判
DateTime endTime = input.EndTime.AddTicks(-1);
db.Table.Where(x => x.Time >= input.StartTime && x.Time <= endTime);推薦寫法
csharp
// ✅ 推薦：語意清晰且具備通用性
db.Table.Where(x => x.Time >= input.StartTime && x.Time < input.EndTime);時間精細度導致的陷阱
當資料庫欄位為 datetime 時，其毫秒值僅會是 0、3 或 7。若查詢條件傳入的變數精度高於欄位（例如使用 datetime2 變數查詢 datetime 欄位），SQL Server 會自動將變數進位，導致查詢結果與預期不符。
此外，若使用 Dapper 或 ADO.NET，底層會根據型別推斷機制將參數轉為 datetime，若未明確指定型別，可能導致在 WHERE 條件中出現非預期的進位誤差。因此，統一使用 < 運算子進行區間查詢是規避此類精度陷阱的最佳實踐。
異動歷程
- 初版文件建立。